setwd("~/Documents/replication_packages/thesis/")
db <- 'm5s_forum_apr2015.sqlite'
db_anonym <- 'm5s_forum_apr2015_anonymised.sqlite'

sqliteGetTable <- function(database, table) {
  require(DBI)
  require(RSQLite)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1)
  dbClearResult(query)
  dbDisconnect(con)
  return(result)
}

sqliteListTables <- function(database, table) {
  require(DBI)
  require(RSQLite)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  return(dbListTables(con))
}

sqliteWriteTable <- function(database, table_name, df) {
  require(DBI)
  require(RSQLite)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  dbWriteTable(con, table_name, df)
}

tables <- sqliteListTables(db)

author_tbl <- sqliteGetTable(db, 'author')
comment_tbl <- sqliteGetTable(db, 'comment')
commentAuthor_tbl <- sqliteGetTable(db, 'commentAuthor')
crawledUrl_tbl <- sqliteGetTable(db, 'crawledUrl')
thread_tbl <- sqliteGetTable(db, 'thread')

library(openssl)
author_tbl$name <- md5(author_tbl$name)
author_tbl$url <- md5(author_tbl$url)

comment_tbl$authorUrl <- md5(comment_tbl$authorUrl)

commentAuthor_tbl$authorUrl <- md5(commentAuthor_tbl$authorUrl)
commentAuthor_tbl$name <- md5(commentAuthor_tbl$name)

thread_tbl$authorUrl <- md5(thread_tbl$authorUrl)

sqliteWriteTable(db_anonym, 'author', author_tbl)
sqliteWriteTable(db_anonym, 'comment', comment_tbl)
sqliteWriteTable(db_anonym, 'commentAuthor', commentAuthor_tbl)
sqliteWriteTable(db_anonym, 'crawledUrl', crawledUrl_tbl)
sqliteWriteTable(db_anonym, 'thread', thread_tbl)
